package com.oldroid.apps.getit.provider;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

import com.oldroid.apps.getit.provider.DownloadsContract.BookmarksColumns;
import com.oldroid.apps.getit.provider.DownloadsContract.Downloads;
import com.oldroid.apps.getit.provider.DownloadsContract.DownloadsColumns;
import com.oldroid.apps.getit.provider.DownloadsContract.Downloads_Tags_Columns;
import com.oldroid.apps.getit.provider.DownloadsContract.TagsColumns;

public class DownloadsDatabase extends SQLiteOpenHelper {
	private static final String TAG = "DownloadsDatabase";
	private static final String DATABASE_NAME = "downloads_log.db";
	private static final int DATABASE_VERSION = 2;

	interface Tables {
		String DOWNLOADS = "downloads";
		String TAGS = "tags";
		String DOWNLOADS_TAGS = "downloads_tags";
		String BOOKMARKS = "bookmarks";
	}

	public DownloadsDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		/*
		 * Create a downloads table
		 */
		db.execSQL("CREATE TABLE " + Tables.DOWNLOADS + " (" + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ DownloadsColumns.FILENAME + " TEXT, " + DownloadsColumns.URI + " TEXT NOT NULL, "
				+ DownloadsColumns.CREATED + " INTEGER, " + DownloadsColumns.MODIFIED + " INTEGER, "
				+ DownloadsColumns.TOTAL_BYTES + " INTEGER, " + DownloadsColumns.STARRED + " BOOLEAN DEFAULT 0, "
				+ DownloadsColumns.DELETED + " BOOLEAN)");

		/*
		 * Create a tags table
		 */
		db.execSQL("CREATE TABLE " + Tables.TAGS + " (" + BaseColumns._ID + " INTEGER PRIMARY KEY, "
				+ TagsColumns.TAG + " TEXT UNIQUE)");

		/*
		 * Create a downloads_tags table
		 */
		db.execSQL("CREATE TABLE " + Tables.DOWNLOADS_TAGS + " (" + BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + Downloads_Tags_Columns.TAG_ID + " INTEGER, "
				+ Downloads_Tags_Columns.DOWNLOADS_ID + " INTEGER)");

		/*
		 * Create a bookmarks table
		 */
		db.execSQL("CREATE TABLE " + Tables.BOOKMARKS + " (" + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ BookmarksColumns.FILELOCATION + " TEXT, " + BookmarksColumns.NAME + " TEXT)");

		/** Create several indexes to make it faster **/
		db.execSQL("CREATE INDEX " + DownloadsContract.IDX_DOWNLOADS__CREATED + " ON " + Tables.DOWNLOADS + "("
				+ DownloadsColumns.CREATED + ")");
		db.execSQL("CREATE INDEX " + DownloadsContract.IDX_DOWNLOADS__MODIFIED + " ON " + Tables.DOWNLOADS + "("
				+ DownloadsColumns.MODIFIED + ")");
		db.execSQL("CREATE INDEX " + DownloadsContract.IDX_DOWNLOADS_TAGS__TAG_ID + " ON " + Tables.DOWNLOADS_TAGS
				+ "(" + Downloads_Tags_Columns.TAG_ID + ")");
		db.execSQL("CREATE INDEX " + DownloadsContract.IDX_DOWNLOADS_TAGS__DOWNLOADS_ID + " ON "
				+ Tables.DOWNLOADS_TAGS + "(" + Downloads_Tags_Columns.DOWNLOADS_ID + ")");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + Tables.DOWNLOADS);
		db.execSQL("DROP TABLE IF EXISTS " + Tables.TAGS);
		db.execSQL("DROP TABLE IF EXISTS " + Tables.DOWNLOADS_TAGS);
		db.execSQL("DROP TABLE IF EXISTS " + Tables.BOOKMARKS);
		onCreate(db);

	}

}
